﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace BookSystem.CommomHelper
{
    class SQLHelper
    {

        //connection对象
        private static SqlConnection conn;
        private static SqlCommand cmd;

        //======数据库的CRUD=====

        //查=》简单查询
        public static int ExecuteNonQuery(String sqlStr)
        {

            int i = 0;

            if ((conn = getConnnection()) != null)
            {

                cmd = new SqlCommand(sqlStr, conn);
                i = cmd.ExecuteNonQuery();
            }

            return i;

        }

        //查=》参数化查询
        public static int ExecuteNonQuery(String sqlStr, params SqlParameter[] parameter)
        {

            int i = 0;

            if ((conn = getConnnection()) != null)
            {

                cmd = new SqlCommand(sqlStr, conn);

                //加载参数
                cmd.Parameters.AddRange(parameter);

                i = cmd.ExecuteNonQuery();
            }

            //关闭数据库连接，并且返回i值
            conn.Close();
            return i;

        }

        //查=》参数化、存储过程
        public static int ExecuteNonQuery(SqlParameter[] parameter, String sp_name)
        {

            int i = 0;

            conn = getConnnection();

            cmd = new SqlCommand(sp_name, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddRange(parameter);

            i = cmd.ExecuteNonQuery();

            conn.Close();
            return i;
        }

        //=========查询，并返回DataTable========

        //=========查询，并返回DataTable========

        //查=》sql语句查询，并返回一个Datable
        /// <summary>
        /// 执行查询，返回要查询的结果集
        /// </summary>
        /// <param name="sql">sql语句</param>
        /// <returns></returns>
        public static DataTable ExecuteTable(string sqlStr)
        {
            // 保存要查询的结果
            DataTable table = new DataTable("dt");

            conn = getConnnection();

            cmd = new SqlCommand(sqlStr, conn);


            // 创建一个适配器对象，用以将结果填充到DataTable中
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            //用SqlDataAdapt填充到DataTable对象中
            adapter.Fill(table);


            cmd.Dispose();
            conn.Close();
            return table;
        }

        //查=》参数化查询，并返回一个Datable
        /// <summary>
        /// 创建一个DataTable对象
        /// </summary>
        /// <param name="M_str_par_SqlStr">SQL语句</param>
        /// <param name="M_str_table">表名</param>
        /// <returns>返回DataSet对象</returns>
        /// <summary>
        /// 执行查询，返回要查询的结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="par_CommandType">SQL语句的类型（SQL文本命令或存储过程）</param>
        /// <returns></returns>
        public static DataTable ExecuteTable(String sqlStr, params SqlParameter[] parameter)
        {
            // 保存要查询的结果
            DataTable table = new DataTable("dt");

            conn = getConnnection();

            cmd = new SqlCommand(sqlStr, conn);
            cmd.Parameters.AddRange(parameter);

            // 创建一个适配器对象，用以将结果填充到DataTable中
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            //用SqlDataAdapt填充到DataTable对象中
            adapter.Fill(table);


            cmd.Dispose();
            conn.Close();
            return table;
        }

        //参数化查询、存储过程，并返回一个Datable
        /// <summary>
        /// 执行查询，返回要查询的结果集
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="par_CommandType">SQL语句的类型（SQL文本命令或存储过程）</param>
        /// <param name="parameters">参数列表</param>
        /// <returns></returns>
        public static DataTable ExecuteTable(SqlParameter[] parameter, String sqlStr)
        {
            // 保存要查询的结果
            DataTable table = new DataTable("dt");

            conn = getConnnection();
            //SqlCommand command;

            cmd = new SqlCommand(sqlStr, conn);
            cmd.CommandType = CommandType.StoredProcedure;

            // 添加到命令对象中

            cmd.Parameters.AddRange(parameter);


            // 创建一个适配器对象，用以将结果填充到DataTable中
            SqlDataAdapter adapter = new SqlDataAdapter(cmd);

            //用SqlDataAdapt填充到DataTable对象中
            adapter.Fill(table);


            cmd.Dispose();
            conn.Close();
            return table;

        }




        //+++++++++获取连接对象Connection+++++++++
        //get到SqlConnection
        public static SqlConnection getConnnection()
        {

            conn = new SqlConnection();

            try
            {
                //连接配置

                conn.ConnectionString = "Data Source = .; Initial Catalog = Books; Integrated Security = true";

                //连接测试
                conn.Open();


            }
            catch (SqlException SQL)
            {

                Console.WriteLine("连接数据库失败");
                conn.Close();

            }

            return conn;
        }


    }
}
